![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Chapter 6
|
View | Description |
---|---|
V$ACCESS | Information about locked objects in the database and who is accessing them. |
V$CIRCUIT | Information about virtual circuits. A virtual circuit is a connection into the database through dispatchers and servers. |
V$DB_OBJECT_CACHE | Database objects (tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers) cached in the library cache. |
V$DISPATCHER | Dispatcher process information. |
V$FILESTAT | Information on file read/write statistics. This information can be enhanced by setting the parameter TIMED_STATISTICS equal to TRUE. Because setting this parameter degrades performance, use it only if you feel it is necessary to debug a problem. |
V$LATCH | Information about each type of latch; used to indicate latch contention. |
V$LIBRARYCACHE | Statistics about library cache management. Look here for information about the number of library cache hits. |
V$LOCK | Information about locks and resources; does not include information about DDL locks. |
V$QUEUE | Information about the multithreaded message queues. Look here for information necessary to determine average wait time per item. |
V$REQDIST | Histogram of request time, divided into 12 buckets; can provide valuable information about request times. |
V$ROLLSTAT | Statistics for all online rollback segments. Look here for information about whether rollback segments are properly configured. |
V$ROWCACHE | Statistics for all data dictionary activity. |
V$SESSION_WAIT | List of resources or events for which active sessions are waiting; can be useful in debugging a particular problem. |
V$SESSTAT | The current statistic values for each current session; used with V$STATNAME. |
V$SESS_IO | I/O statistics for each user session. |
V$SYSSTAT | The current system-wide value for each statistic in V$SESSTAT; also used with V$STATNAME. |
V$WAITSTAT | Block contention statistics. This information is stored only when the parameter TIMED_STATISTICS is set to TRUE. Setting this parameter causes extra overhead and degrades performance; use it only if you feel it is necessary to debug a problem. |
The dynamic performance tables include many more tables than are mentioned here. Most tables are used internally and are not of use in performance tuning unless you are debugging a specific problem.
Oracle offers several performance monitoring tools. You automatically get SQL*DBA as part of the Oracle RDBMS product, which includes a monitor interface into the V$ tables. You can also get Server Manager from Oracle (which eventually will replace SQL*DBA as the standard administrative tool). Server Manager (introduced in Oracle version 7.1) is similar to SQL*DBA but provides an optional Graphical User Interface version.
Oracle recently introduced SNMP (Simple Network Management Protocol) agents. These agents (as well as any of the third-party vendors who have been working with Oracle) provide an interface into the V$ views by using an SNMP management console. Also new from Oracle is a graphical version of SQL Trace. The SQL Trace tool is designed to help track down and fix inefficient SQL statements in your application. Of course, the EXPLAIN PLAN command is always useful in analyzing SQL code.
You can use the SQL*DBA monitor command to display real-time information about locks, enqueues, processes, file I/O, and other system statistics. Monitor is used as a shortcut to display information stored in the V$ tables. Monitor has been around as long as SQL*DBA; with the emergence of Server Manager, Monitor may not be of use much longer.
Server Manager is now available on several platforms. Server Manager is similar to SQL*DBA in that it is designed to assist in the administration of the Oracle database. But where SQL*DBA is character based, Server Manager provides a Graphical User Interface. From Server Manager, you can display much of the information available in the V$ tables.
Previous | Table of Contents | Next |
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |